/**
 * 
 */
package com.teradata.tool.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.teradata.tool.bean.IdMappingInfoBean;

/**
 * @author wang
 *
 */
public class IdMappingDao {
	private static final String	QUERY_BY_TARGET_TBL_NAME_CN	= "SELECT * FROM ?.? WHERE TARGET_TBL_NAME_CN=?";
	private static final String	QUERY_BY_SOURCE_INFO		= "SELECT * FROM ?.? WHERE SRC_CODE=? AND SRC_TBL_NAME=?";

	public static List<IdMappingInfoBean> queryByTargetTblNameCn(Connection conn, String databaseName,
			String tableName, String targetTblNameCn) throws SQLException {
		List<IdMappingInfoBean> ret = new ArrayList<IdMappingInfoBean>();
		PreparedStatement stat = conn.prepareStatement(QUERY_BY_TARGET_TBL_NAME_CN);
		try {
			stat.setString(1, databaseName);
			stat.setString(2, tableName);
			stat.setString(3, targetTblNameCn);
			ResultSet rs = stat.executeQuery();
			try {
				while (rs.next()) {
					IdMappingInfoBean IdMappingInfoBean = new IdMappingInfoBean();
					int i = 1;
					IdMappingInfoBean.setDesigner(rs.getString(i++));
					IdMappingInfoBean.setSrc_sys(rs.getString(i++));
					IdMappingInfoBean.setSrc_app(rs.getString(i++));
					IdMappingInfoBean.setSrc_code(rs.getString(i++));
					IdMappingInfoBean.setOwner_t(rs.getString(i++));
					IdMappingInfoBean.setSrc_tbl_name(rs.getString(i++));
					IdMappingInfoBean.setSrc_tbl_name_cn(rs.getString(i++));
					IdMappingInfoBean.setSrc_tbl_bi_info(rs.getString(i++));
					IdMappingInfoBean.setData_catalog(rs.getString(i++));
					IdMappingInfoBean.setBi_area(rs.getString(i++));
					IdMappingInfoBean.setIs_increase(rs.getString(i++));
					IdMappingInfoBean.setIncrease_condition(rs.getString(i++));
					IdMappingInfoBean.setSrc_del_policy(rs.getString(i++));
					IdMappingInfoBean.setSrc_bar_policy(rs.getString(i++));
					IdMappingInfoBean.setSrc_field_no(rs.getString(i++));
					IdMappingInfoBean.setSrc_col_name(rs.getString(i++));
					IdMappingInfoBean.setSrc_col_name_cn(rs.getString(i++));
					IdMappingInfoBean.setSrc_col_type(rs.getString(i++));
					IdMappingInfoBean.setIs_pk(rs.getString(i++));
					IdMappingInfoBean.setIs_code(rs.getString(i++));
					IdMappingInfoBean.setStd_code(rs.getString(i++));
					IdMappingInfoBean.setCol_char_set(rs.getString(i++));
					IdMappingInfoBean.setCol_char_set_trans(rs.getString(i++));
					IdMappingInfoBean.setSrc_col_desc(rs.getString(i++));
					IdMappingInfoBean.setSrc_dq(rs.getString(i++));
					IdMappingInfoBean.setIs_extract(rs.getString(i++));
					IdMappingInfoBean.setTarget_tbl_name_cn(rs.getString(i++));
					IdMappingInfoBean.setTarget_tbl_name(rs.getString(i++));
					IdMappingInfoBean.setTarget_col_name_cn(rs.getString(i++));
					IdMappingInfoBean.setTarget_col_name(rs.getString(i++));
					IdMappingInfoBean.setMapping_rule(rs.getString(i++));
					IdMappingInfoBean.setSubject(rs.getString(i++));
					IdMappingInfoBean.setStage_tbl_name(rs.getString(i++));
					IdMappingInfoBean.setStage_col_name(rs.getString(i++));
					IdMappingInfoBean.setStage_col_type(rs.getString(i++));
					IdMappingInfoBean.setStage_col_format(rs.getString(i++));
					IdMappingInfoBean.setRecord_number(rs.getString(i++));
					IdMappingInfoBean.setRecord_length(rs.getString(i++));
					IdMappingInfoBean.setSrc_db_type(rs.getString(i++));
					IdMappingInfoBean.setTarget_db_type(rs.getString(i++));
					IdMappingInfoBean.setSsa_analyzer(rs.getString(i++));
					ret.add(IdMappingInfoBean);
				}
			} finally {
				rs.close();
			}
		} finally {
			stat.close();
		}
		return ret;
	}

	public static List<IdMappingInfoBean> queryBySourceInfo(Connection conn, String databaseName, String tableName,
			String srcCode, String sourceTableName) throws SQLException {
		List<IdMappingInfoBean> ret = new ArrayList<IdMappingInfoBean>();
		PreparedStatement stat = conn.prepareStatement(QUERY_BY_SOURCE_INFO);
		try {
			stat.setString(1, databaseName);
			stat.setString(2, tableName);
			stat.setString(3, srcCode);
			stat.setString(4, sourceTableName);
			ResultSet rs = stat.executeQuery();
			try {
				while (rs.next()) {
					IdMappingInfoBean IdMappingInfoBean = new IdMappingInfoBean();
					int i = 1;
					IdMappingInfoBean.setDesigner(rs.getString(i++));
					IdMappingInfoBean.setSrc_sys(rs.getString(i++));
					IdMappingInfoBean.setSrc_app(rs.getString(i++));
					IdMappingInfoBean.setSrc_code(rs.getString(i++));
					IdMappingInfoBean.setOwner_t(rs.getString(i++));
					IdMappingInfoBean.setSrc_tbl_name(rs.getString(i++));
					IdMappingInfoBean.setSrc_tbl_name_cn(rs.getString(i++));
					IdMappingInfoBean.setSrc_tbl_bi_info(rs.getString(i++));
					IdMappingInfoBean.setData_catalog(rs.getString(i++));
					IdMappingInfoBean.setBi_area(rs.getString(i++));
					IdMappingInfoBean.setIs_increase(rs.getString(i++));
					IdMappingInfoBean.setIncrease_condition(rs.getString(i++));
					IdMappingInfoBean.setSrc_del_policy(rs.getString(i++));
					IdMappingInfoBean.setSrc_bar_policy(rs.getString(i++));
					IdMappingInfoBean.setSrc_field_no(rs.getString(i++));
					IdMappingInfoBean.setSrc_col_name(rs.getString(i++));
					IdMappingInfoBean.setSrc_col_name_cn(rs.getString(i++));
					IdMappingInfoBean.setSrc_col_type(rs.getString(i++));
					IdMappingInfoBean.setIs_pk(rs.getString(i++));
					IdMappingInfoBean.setIs_code(rs.getString(i++));
					IdMappingInfoBean.setStd_code(rs.getString(i++));
					IdMappingInfoBean.setCol_char_set(rs.getString(i++));
					IdMappingInfoBean.setCol_char_set_trans(rs.getString(i++));
					IdMappingInfoBean.setSrc_col_desc(rs.getString(i++));
					IdMappingInfoBean.setSrc_dq(rs.getString(i++));
					IdMappingInfoBean.setIs_extract(rs.getString(i++));
					IdMappingInfoBean.setTarget_tbl_name_cn(rs.getString(i++));
					IdMappingInfoBean.setTarget_tbl_name(rs.getString(i++));
					IdMappingInfoBean.setTarget_col_name_cn(rs.getString(i++));
					IdMappingInfoBean.setTarget_col_name(rs.getString(i++));
					IdMappingInfoBean.setMapping_rule(rs.getString(i++));
					IdMappingInfoBean.setSubject(rs.getString(i++));
					IdMappingInfoBean.setStage_tbl_name(rs.getString(i++));
					IdMappingInfoBean.setStage_col_name(rs.getString(i++));
					IdMappingInfoBean.setStage_col_type(rs.getString(i++));
					IdMappingInfoBean.setStage_col_format(rs.getString(i++));
					IdMappingInfoBean.setRecord_number(rs.getString(i++));
					IdMappingInfoBean.setRecord_length(rs.getString(i++));
					IdMappingInfoBean.setSrc_db_type(rs.getString(i++));
					IdMappingInfoBean.setTarget_db_type(rs.getString(i++));
					IdMappingInfoBean.setSsa_analyzer(rs.getString(i++));
					ret.add(IdMappingInfoBean);
				}
			} finally {
				rs.close();
			}
		} finally {
			stat.close();
		}
		return ret;
	}
}
